using System;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.IO;
using System.Xml;
using System.Data.SqlClient;
using Decav.Gatsb.Data;

namespace Decav.Gatsb.SuperPagesParser
{
    /// <summary>
    /// Pulls pages from the http://api.superpages.com XML website into the Locations table in the database.
    /// </summary>
    public class SuperPagesAdapter
    {
        public SuperPagesAdapter(string firstPage, string outputDir, LocationType type)
        {
            if (firstPage == null)
                throw new ArgumentNullException("firstPage");

            if (outputDir == null)
                throw new ArgumentNullException("outputDir");
        

            _firstPageUrl = firstPage;
            _outputDir = outputDir;
            _type = type;
        }

        private LocationType _type;
        private string _outputDir;
        private string _firstPageUrl;

        /// <summary>
        /// Gets all the listings in the specified page, and all paginated pages after that page.
        /// </summary>
        public void Run()
        {
            bool noError = true;
            int page = 0;
            while (noError)
            {
                Console.WriteLine("Page {0}", page);
                string xml = GetXmlPage(_firstPageUrl + "&PI=" + page);
                SaveXml(xml, 0);
                XmlDocument document = new XmlDocument();
                document.LoadXml(xml);
                //document.Load("c:\\users\\andre\\desktop\\SuperPAges_0.xml");

                if (!ParseDocument(document))
                    return;

                page += 15;
            }
        }

        private void SaveXml(string xml, int page)
        {
            File.WriteAllText(Path.Combine(_outputDir, string.Format("SuperPages_{0}.xml", page)), xml);
        }

        private bool ParseDocument(XmlDocument document)
        {
            XmlNodeList listings = document.SelectNodes("spPage/listingPage/listing");

            if (listings.Count == 0)
                return false;

            foreach (XmlNode listing in listings)
                ParseListing(listing);

            return true;
        }

        private void ParseListing(XmlNode node)
        {
            XmlNode detail = node["lDetail"];
            XmlNode address = detail["addr"];
            XmlNodeList cats = node.SelectNodes("inCat/cat");

            string url = (detail["ad"] == null || detail["ad"]["tag"] == null) ? null : detail["ad"]["tag"].InnerText;
            string name = detail["name"].InnerText;
            string glat = detail["latitude"] == null ? null : detail["latitude"].InnerText;
            string glong = detail["longitude"] == null ? null : detail["longitude"].InnerText;

            string street = null, city = null, state = null, zip = null;
            if (address != null)
            {
                street = address["st"] == null ? null : address["st"].InnerText;
                city = address["city"].InnerText;
                state = address["state"].InnerText;
                zip = address["zip"].InnerText;
            }

            if (city == null)
                return;

            XmlNode phone = detail.SelectSingleNode("ph[@text='P']");
            string tel = null;
            if (phone != null)
                tel = phone["area"].InnerText + phone["exch"].InnerText + phone["line"].InnerText;

            List<string> categories = new List<string>();
            foreach (XmlNode cat in cats)
            {
                categories.Add(cat["name"].InnerText);
            }

            Guid locationId = Guid.NewGuid();
            using (SqlConnection cxn = new SqlConnection("Server=(local);Database=Gatsb;Trusted_Connection=Yes"))
            {
                cxn.Open();
                using (SqlCommand cmd = new SqlCommand("dbo.CreateLocation", cxn))
                {
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@LocationId", locationId);
                    cmd.Parameters.AddWithValue("@Type", (int)_type);

                    if (glat != null)
                    {
                        decimal glatd = decimal.Parse(glat) / 1000000;
                        decimal glongd = decimal.Parse(glong) / 1000000;
                        cmd.Parameters.AddWithValue("@Coordinates", "{" + glatd.ToString() + "," + glongd.ToString() + "}");
                    }

                    if (name.Length > 80)
                        name = name.Substring(0, 80);

                    cmd.Parameters.AddWithValue("@Name", name);
                    cmd.Parameters.AddWithValue("@Description", DBNull.Value);
                    cmd.Parameters.AddWithValue("@Address1", street);
                    cmd.Parameters.AddWithValue("@City", city);
                    cmd.Parameters.AddWithValue("@State", state);
                    cmd.Parameters.AddWithValue("@PostalCode", zip);
                    cmd.Parameters.AddWithValue("@PhoneNumber", tel);
                    cmd.Parameters.AddWithValue("@Url", url);

                    cmd.ExecuteNonQuery();
                }

                foreach (string cat in categories)
                {
                    using (SqlCommand cmd = new SqlCommand("dbo.TagLocation", cxn))
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@LocationId", locationId);
                        cmd.Parameters.AddWithValue("@Tag", cat);
                        cmd.ExecuteNonQuery();
                    }
                }

                cxn.Close();
            }
        }

        private string GetXmlPage(string url)
        {
            HttpWebRequest req = (HttpWebRequest)HttpWebRequest.Create(url);

            using (HttpWebResponse response = (HttpWebResponse)req.GetResponse())
            {
                using (Stream responseStream = response.GetResponseStream())
                    return new StreamReader(responseStream).ReadToEnd();
            }
        }
    }
}
